const express = require('express');

//1.创建路由对象
const router = express.Router();

//引入axios
const axios = require('axios');


//传入图片模块
var bodyParser = require('body-parser'); // 这个模块是获取post请求传过来的数据。
var formidable = require('formidable');
var fs = require('fs');


//数据库模块
//1.导入mysql模块
const mysql = require('mysql')
    //2.建立与MySQL数据库的连接
const db = mysql.createPool({
    // host: '127.0.0.1',
    host: '10.0.224.12',
    user: 'david',
    password: 'David1234',
    database: 'hsdata',
    port: '3306'
})

// 检测mysql模块能否正常工作
db.query('SELECT 1', (err, results) => {
        if (err) return console.log(err.message)
        console.log(results, "数据库正常工作", )
    })
    //测试接口
router.get('/test', (req, res) => {
    console.log("open-id!!!", req.headers['x-wx-openid'])
    console.log("测试数据成功输出!")
    res.send("测试数据成功返回!")
})

//用户登录初始
// router.get('/login_first', (req, res) => {
//         let code = req.query.code
//         let appid = "wxf0c51f4340673571"
//         let secret = "b1a7e7587d5513430d026e0612bcc070"
//         let session_key = ""
//         let openid = ""
//         axios.get('https://api.weixin.qq.com/sns/jscode2session?appid=' + appid + '&secret=' + secret + '&js_code=' + code + '&grant_type=authorization_code')
//             .then(function(response) {
//                 console.log("axios请求成功!", response.data);
//                 // session_key = response.data.session_key
//                 openid = response.data.openid
//                 res.send(openid)
//             })
//             .catch(error => {
//                 console.log(error);
//             });
//     })
//用户登录, 传入openid
router.get('/login', (req, res) => {
    //判断数据库是否有该openid
    // let res_data = [];
    db.query('select count(*) as num from user where openid=?', req.headers['x-wx-openid'], (err, results) => {
            if (err) return console.log("失败了", err.message)
                //表中查找openid,没有则插入新用户
                // console.log("成功", results[0].num)
            if (results[0].num == 0) {
                const newuser = { openid: req.headers['x-wx-openid'], status: 0, days: "2022-02-02 10:10:10", times: 0, name: req.headers['x-wx-openid'] }
                db.query('INSERT INTO user SET ?', newuser, (err, results) => {
                    if (err) return console.log(err.message) //失败
                    if (results.affectedRows === 1) { console.log('插入数据成功') } //成功
                    res.send(results)
                })
            } else {
                db.query('SELECT * FROM user WHERE openid=?', req.headers['x-wx-openid'], (err, results) => {
                    // if (err) return console.log(err.message) //失败
                    if (err) {
                        res.send("失败了2")
                        return
                    }
                    // res.send()
                    res.send(results)
                })
            }
        })
        // res.send(res_data)
});
router.get('/getPjMsg', (req, res) => {
    db.query('SELECT * FROM course WHERE class_date >= date_sub(now(), interval 2 day) AND class_date <= date_sub(now(), interval 1 day) AND user1=? AND pj_status=0;',req.query.name, (err, results) => {
        if (err) return console.log("失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("成功", )
        let ret={showPj:false}
        ret.showPj=results.length>0
        res.send(ret)
    })
});
//转换为管理员
router.get('/change', (req, res) => {
    console.log(req.query.code)
    db.query('UPDATE user SET status=1 WHERE id=?', parseInt(req.query.code), (err, results) => {
        if (err) return console.log(err.message) //失败
        console.log('管理员数据成功!') //成功
        res.send(results)
    })
});
//返回所有学生数据
router.get('/student', (req, res) => {
    db.query('select * from user', (err, results) => {
        if (err) return console.log("失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("成功", results[0])
        res.send(results)
    })
});
//保存名字
router.get('/savename', (req, res) => {
    console.log(req.query.name, req.query.code)
    db.query('UPDATE user SET name=? WHERE id=?', [req.query.name, req.query.code], (err, results) => {
        if (err) {
            res.send(err)
            return console.log("失败了", err.message)
        }
        //表中查找openid,没有则插入新用户
        console.log("成功", results)
        res.send(results)
    })
});
//修改时间
router.get('/change_date', (req, res) => {
    let date = req.query.date + " 00:00:00"
    db.query('UPDATE user SET days=? WHERE id=?', [date, req.query.code], (err, results) => {
        if (err) return console.log("日期更新失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("日期更新成功")
        res.send(results)
    })
});

//修改课程数量
router.get('/change_times', (req, res) => {
    db.query('UPDATE user SET times=? WHERE id=?', [req.query.times, req.query.code], (err, results) => {
        if (err) return console.log("次数更新失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("次数更新成功")
        res.send(results)
    })
});
//请假次数
router.get('/change_leave_num', (req, res) => {
    db.query('UPDATE user SET leave_num=? WHERE id=?', [req.query.leave_num, req.query.code], (err, results) => {
        if (err) return console.log("次数更新失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("次数更新成功")
        res.send(results)
    })
});
//修改用户卡类型
router.get('/change_user_type', (req, res) => {
    db.query('UPDATE user SET type=? WHERE id=?', [req.query.type, req.query.code], (err, results) => {
        if (err) return console.log("卡类型更新失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("卡类型更新成功")
        res.send(results)
    })
});
//返回所有教练数据
router.get('/master', (req, res) => {
    db.query('select * from master', (err, results) => {
        if (err) return console.log("失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("成功", results[0])
        res.send(results)
    })
});
//删除学员 
router.get('/student_del', (req, res) => {
    db.query('DELETE FROM user WHERE id=?', req.query.id, (err, results) => {
        if (err) return console.log("学员删除失败", err.message)
            //表中查找openid,没有则插入新用户
        console.log("学员删除成功")
        res.send(results)
    })
});
//请假
router.get('/user_leave', (req, res) => {
    let {leave_start_date,leave_end_date,id} = req.query
    db.query('select days,leave_num from user WHERE id=?',id,(err,rese)=>{
        if (err) return console.log("请假提交失败了", err.message)
        let user_info =rese[0];
        console.log("user_info:",user_info)
        let st=new Date(leave_start_date)
        let et=new Date(leave_end_date)
        let time=et.getTime() - st.getTime();
        let new_days=new Date(new Date(user_info.days).getTime()+time)
        let Y = new_days.getFullYear() + "-";
        let M =
        (new_days.getMonth() + 1 < 10
            ? "0" + (new_days.getMonth() + 1)
            : new_days.getMonth() + 1) + "-";
        let D = (new_days.getDate() < 10 ? "0" + new_days.getDate() : new_days.getDate()) + " ";
        let new_date =Y  + M +  D +'10:10:10'
        console.log("new_date:",new_date)

        if(user_info.leave_num >0){
            let leave_num = user_info.leave_num -1
            db.query('UPDATE user SET leave_start_date=?,leave_end_date=?,days=?,leave_num=? WHERE id=?', [leave_start_date, leave_end_date,new_date,leave_num,id], (err, results) => {
                if (err) return console.log("请假提交失败了", err.message)
                    //表中查找openid,没有则插入新用户
                console.log("请假提交成功")
                res.send(results)
            })
        }else{
            res.send({code:1,msg:"请假失败"})
        }
        
    })
    
    
});
//添加教练数据
router.get('/add_master', (req, res) => {
    db.query('INSERT INTO master SET ?', { name: req.query.name }, (err, results) => {
        if (err) return console.log("失败了", err.message)
        console.log("成功", results)
        res.send(results)
    })
});
//编辑教练数据
router.get('/change_master', (req, res) => {
    console.log(req.query.name, req.query.code)
    db.query('UPDATE master SET name=? WHERE id=?', [req.query.name, req.query.code], (err, results) => {
        if (err) return console.log("教练更新失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("教练更新成功")
        res.send(results)
    })
});
//删除教练
router.get('/delete_master', (req, res) => {
    db.query('DELETE FROM master WHERE id=?', req.query.id, (err, results) => {
        if (err) return console.log("教练删除失败", err.message)
            //表中查找openid,没有则插入新用户
        console.log("教练删除成功")
        res.send(results)
    })
});

//获得课程数据
router.get('/course', (req, res) => {
    db.query('select * from course ', (err, results) => {
        if (err) return console.log("课程获取失败", err.message)
        console.log("课程获取成功")
        res.send(results)
    })
});
//按时间筛选课程信息
router.get('/day_course_info', (req, res) => {
    db.query('select * from course where class_date=? and status=1 ORDER BY start_time', req.query.ymd, (err, results) => {
        if (err) return console.log("课程获取失败", err.message)
        console.log("课程获取成功")
        res.send(results)
    })
});
//管理 课程 按时间筛选课程信息
router.get('/admin_course_info', (req, res) => {
    db.query('select * from course where class_date=? ORDER BY start_time', req.query.ymd, (err, results) => {
        if (err) return console.log("课程获取失败", err.message)
        console.log("课程获取成功")
        res.send(results)
    })
});
//获得教练名称生成课程
router.get('/add_course_by_coach', (req, res) => {
    console.log(req.query)
    for (let time = 10; time < 22; time++) {
        let intime = `00:${time}:00`
        db.query('INSERT INTO course (start_time,class_date,master,user_num) VALUES (?,?,?,?)', [intime, req.query.ymd, req.query.coach, 1], (err, results) => {
            if (err) return console.log("课程获取失败", err.message)
            console.log("添加成功")
        })
    }
    return
});
router.get('/add_course_by_coach_hide', (req, res) => {
    console.log(req.query)
    for (let time = 10; time < 22; time++) {
        let intime = `00:${time}:00`
        db.query('INSERT INTO course (start_time,class_date,master,user_num,status) VALUES (?,?,?,?,?)', [intime, req.query.ymd, req.query.coach, 1,0], (err, results) => {
            if (err) return console.log("课程获取失败", err.message)
            console.log("添加成功")
        })
    }
    return
});
//删除课程
router.get('/delete_class', (req, res) => {
    db.query('DELETE FROM course WHERE classid=?', req.query.classid, (err, results) => {
        if (err) return console.log("课程删除失败", err.message)
            //表中查找openid,没有则插入新用户
        console.log("课程删除成功")
        res.send(results)
    })
});
//添加单独课程
router.get('/single_add', (req, res) => {
    db.query('INSERT INTO course (start_time,class_date,master,user_num) VALUES (?,?,?,?)', [req.query.time, req.query.ymd, req.query.coach, req.query.num], (err, results) => {
        if (err) return console.log("课程获取失败", err.message)
        console.log("添加成功")
        res.send(results)
    })
});
//添加单独课程 不可看
router.get('/single_add_hide', (req, res) => {
    db.query('INSERT INTO course (start_time,class_date,master,user_num,status) VALUES (?,?,?,?,?)', [req.query.time, req.query.ymd, req.query.coach, req.query.num,0], (err, results) => {
        if (err) return console.log("课程获取失败", err.message)
        console.log("添加成功")
        res.send(results)
    })
});
//预约课程
router.get('/book_class', (req, res) => {
    console.log("学员", req.query.username)
    db.query('select leave_start_date,leave_end_date,type from user WHERE name=?',req.query.username,(err,result)=>{
        if (err) return console.log("失败了", err.message)
        let user=result[0]
        
        db.query('select user1,user2,user_num,class_date,start_time from course WHERE classid=?', req.query.classid, (err, results) => {
            if (err) return console.log("失败了", err.message)
            if(user.leave_start_date && user.leave_end_date){
                let date= new Date(results[0].class_date);
                let nowTime = Date.parse(date)-(8 * 60 * 60 * 1000);
                let startTime = Date.parse(user.leave_start_date)-(8 * 60 * 60 * 1000);
                let endTime = Date.parse(user.leave_end_date)-(8 * 60 * 60 * 1000);
                
                console.log("nowTime >= startTime",nowTime >= startTime)
                console.log("nowTime , startTime",nowTime , startTime)
                console.log("nowTime <= endTime",nowTime <= endTime)
                console.log("nowTime , endTime",nowTime , endTime)
                if (nowTime >= startTime && nowTime <= endTime) {
                    res.send({code:1,msg:'预约失败,会员卡冻结'})
                    return
                }
            }
            console.log("这是预约时间段：",results[0].start_time)
            console.log("这是用户类型：",user.type)
            if(results[0].start_time == '00:19:00' || results[0].start_time == '00:20:00'){
                if(user.type==1){

                    return res.send({code:1,msg:'预约失败,您的课卡请在非高峰时段使用'})
                }
            }
            
            if (results[0].user1 == "null" ) {
                console.log("第一个学员", req.query.username)
                db.query('UPDATE course SET user1=? WHERE classid=?', [req.query.username, req.query.classid], (err, results) => {
                    if (err) return console.log("预约失败了", err.message)
                        //表中查找openid,没有则插入新用户
                    console.log("预约成功1")
                    res.send({code:0,msg:'预约成功'})
                })
            } else if (results[0].user_num == 2 && results[0].user2 == "null") {
                console.log("第二个学员", req.query.username)
                db.query('UPDATE course SET user2=? WHERE classid=?', [req.query.username, req.query.classid], (err, results) => {
                    if (err) return console.log("预约失败了", err.message)
                        //表中查找openid,没有则插入新用户
                    console.log("预约成功2")
                    res.send({code:0,msg:'预约成功'})
                })
            }else{
                console.log("预约失败了")
                res.send({code:1,msg:'预约失败'})
            }
            console.log("返回user值", results[0])
        })
    })
    

});
//返回所有预约课程信息
router.get('/change_book', (req, res) => {
    db.query('select * from course where user1=? or user2=? ORDER BY class_date DESC', [req.query.username, req.query.username], (err, results) => {
        if (err) return console.log("查询失败", err.message)
            //表中查找openid,没有则插入新用户
        console.log("查找预约成功")
        res.send(results)
    })
});
//查询教练课程数
router.get('/select_num', (req, res) => {
    let {y,m}=req.query
    db.query("SELECT master,COUNT(class_date) as num FROM course WHERE (user1!='null' OR user2!='null' ) AND (YEAR(class_date)=? and month(class_date)=?) GROUP BY master", [y, m], (err, results) => {
        if (err) return console.log("查询失败", err.message)
        console.log("查找成功")
        res.send(results)
    })
});
//评价
router.get('/course_pj', (req, res) => {
    db.query('UPDATE course SET pj_status=?,pj_json=? WHERE classid=?', [1, req.query.pj_json,req.query.classid], (err, results) => {
        if (err) return console.log("评价失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("评价成功")
        res.send(results)
    })

});
//取消预约user1
router.get('/cancel_user1', (req, res) => {
    db.query('UPDATE course SET user1=? WHERE classid=?', ['null', req.query.classid], (err, results) => {
        if (err) return console.log("取消预约失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("取消预约成功")
        res.send(results)
    })

});
//取消预约user2
router.get('/cancel_user2', (req, res) => {
    db.query('UPDATE course SET user2=? WHERE classid=?', ['null', req.query.classid], (err, results) => {
        if (err) return console.log("取消预约失败了", err.message)
            //表中查找openid,没有则插入新用户
        console.log("成功取消预约")
        res.send(results)
    })
});
//上传图片
router.post('/add_picture', (req, res) => {
    console.log(req.files[0], "req数据")
    let old = req.files[0].path //获取path:
    fs.renameSync(old, './public/' + req.files[0].fieldname + '.jpg');
    // fs.renameSync(old, './public/' + req.files[0].path + path.parse(req.files[0].originalname).ext)
    console.log("图片存储了没?")
    res.send("结束")
});
// router.get('/active/:id/:name', (req, res) => {
//     res.send(req.params)
//     console.log("2个动态参数已返回", req.params)
// })
// router.post('/post_json', (req, res) => {
//     console.log("post_json被访问了")
//     console.log(res.body)
// })

//3.将路由对象共享出去
module.exports = router